package com.datacurl
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.EventDispatcher;
	import flash.events.IEventDispatcher;
	import flash.filesystem.File;

	public class DataManager extends EventDispatcher
	{
		private var sqlConn:SQLConnection = new SQLConnection();
		private var dbfile:File = File.applicationStorageDirectory.resolvePath("YAWN.db");
		public function DataManager(target:IEventDispatcher=null)
		{
			super(target);
			
			sqlConn.open(dbfile);
			provisionDB();
			
		}

		[Bindable(event="ircnickChange")]
		public function get ircnicks():Array
		{
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConn;
			stmt.text = "SELECT * FROM ircnick ORDER BY nick";
			stmt.execute();
			return stmt.getResult().data;
		}
		
		public function getIrcnick(nick:String):Array
		{
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConn;
			stmt.text = "SELECT * FROM ircnick where nick = :nick ORDER BY nick";
			stmt.parameters[":nick"] = nick;
			stmt.execute();
			return stmt.getResult().data;
		}

		public function ircnickSubmitted(nick:String):int
		{
			var rtn:uint = 0;

			if(ircnickExists(nick) ){
				rtn = chooseIrcnick(nick);
			} else { 
				//we haven't seen this nick before, add it to the config
				rtn = addIrcnick(nick);
			}
			return rtn;
		}		
		
		public function ircnickExists(nick:String):Boolean{
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConn;
			stmt.text = "SELECT nick FROM ircnick WHERE nick = :nick";
			stmt.parameters[":nick"] = nick;
			stmt.execute();
			//if getResult() returns something, then the nick exists
			return (stmt.getResult().data != null);
			
		}
		
		
		private function addIrcnick(nick:String):Number{
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConn;
				stmt.text = "INSERT INTO ircnick ( nick, timesused, lastused) VALUES ( :nick, :timesused, :lastused )"
				stmt.parameters[":nick"] = nick;
				stmt.parameters[":timesused"] = 1;
				stmt.parameters[":lastused"] = new Date().time;
				stmt.execute();
				return stmt.getResult().rowsAffected;
		}
		
		private function chooseIrcnick(nick:String):Number{
			var selStmt:SQLStatement = new SQLStatement();
			var updStmt:SQLStatement = new SQLStatement();
			selStmt.sqlConnection = sqlConn;
			updStmt.sqlConnection = sqlConn;
			//get the old nick information
				selStmt.text = "SELECT * FROM ircnick where nick = :nick ORDER BY nick";
				selStmt.parameters[":nick"] = nick;
				selStmt.execute();
			var newTimesUsed:Number = selStmt.getResult().data[0].timesused + 1;
			
			//increment the count and add the new date
				updStmt.text = "UPDATE ircnick SET timesused = :timesused, lastused = :lastused WHERE nick = :nick";
				updStmt.parameters[":nick"] = nick;
				updStmt.parameters[":timesused"] = newTimesUsed;
				updStmt.parameters[":lastused"] = new Date().time;
				updStmt.execute();
				return newTimesUsed;
		}
		
		
		
		private function provisionDB():void{
			var stmtArray:Array = new Array();
			//config statements
			stmtArray.push("CREATE TABLE IF NOT EXISTS ircnick ( nick varchar(250), timesused number, lastused number)");
			stmtArray.push("CREATE TABLE IF NOT EXISTS keywordalert ( keyword varchar(250), action number)");
			stmtArray.push("CREATE TABLE IF NOT EXISTS preference ( preference varchar(250), value number)");
			
			//execute statements
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConn;
			for( var i:uint = 0; i < stmtArray.length; i ++){
				stmt.text = stmtArray[i];
				stmt.execute();
			}
			
		}
		
	}
}
